﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class InvoiceDA : IInvoiceDA
    {
        private string BILLING_DATE = "@BILLING_DATE";
        private string BILLING_OBJECT = "@BILLING_OBJECT";
        private string BILLING_OBJECT_ID = "@BILLING_OBJECT_ID";
        private string CREATED_BY = "@CREATED_BY";
        private string DUE_FROM_TO = "@DUE_FROM_TO";
        private string INVOICE_ID = "@INVOICE_ID";
        private string IS_AMOUNT_SUBMIT = "@IS_AMOUNT_SUBMIT";
        private string IS_INVOICE_SUBMIT = "@IS_INVOICE_SUBMIT";
        private string REMARK = "@REMARK";
        private string SQL_CHECK_INVOICE_ID_UNIQUE = " SELECT COUNT(1) FROM @_@INVOICE WHERE INVOICE_ID = @INVOICE_ID ";
        private string SQL_DELETE_INVOICE = " DELETE FROM @_@INVOICE WHERE INVOICE_ID = @INVOICE_ID ";
        private string SQL_INSERT_INVOICE = " INSERT INTO @_@INVOICE ( WH_ID, WH_NAME, INVOICE_ID, BILLING_DATE, BILLING_OBJECT, BILLING_OBJECT_ID, DUE_FROM_TO, STATUS, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,IS_INVOICE_SUBMIT,IS_AMOUNT_SUBMIT) VALUES ( @WH_ID, @WH_NAME, @INVOICE_ID, @BILLING_DATE, @BILLING_OBJECT, @BILLING_OBJECT_ID, @DUE_FROM_TO, @STATUS, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@IS_INVOICE_SUBMIT,@IS_AMOUNT_SUBMIT )  ";
        private string SQL_SELECT_ALL_INVOICE = " SELECT  ROWID, WH_ID, WH_NAME, INVOICE_ID, BILLING_DATE, BILLING_OBJECT, BILLING_OBJECT_ID, DUE_FROM_TO, STATUS, REMARK, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,IS_INVOICE_SUBMIT,IS_AMOUNT_SUBMIT  FROM  @_@INVOICE ";
        private string SQL_SELECT_ALL_INVOICE_LANGUAGE = " SELECT  I.ROWID, I.WH_ID, I.WH_NAME, I.INVOICE_ID, I.BILLING_DATE, I.BILLING_OBJECT, I.BILLING_OBJECT_ID, I.DUE_FROM_TO, I.STATUS, I.REMARK, I.TS, I.CREATED_BY, I.CREATED_DATE, I.UPDATED_BY, I.UPDATED_DATE,I.IS_INVOICE_SUBMIT,I.IS_AMOUNT_SUBMIT,   T.[TEXT] AS INVOICE_STATUS_DESCR,T1.[TEXT] AS BILLING_OBJECT_DESCR,T2.[TEXT] AS DUE_FROM_TO_DESCR FROM  @_@INVOICE I  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = I.STATUS AND SCD.SYS_CODE_TYPE = 'InvoiceStatus'  LEFT JOIN @_@SYS_CODE_DETAIL SCD1 ON SCD1.SYS_CODE = I.BILLING_OBJECT AND SCD1.SYS_CODE_TYPE = 'BILLINGOBJECT' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = I.DUE_FROM_TO AND SCD2.SYS_CODE_TYPE = 'DUEFROMTO' LEFT JOIN sysadm.TRANSLATION T ON 'InvoiceStatus.'+I.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL'  AND  T.LANG+'-'+T.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T1 ON 'BILLINGOBJECT.'+I.BILLING_OBJECT = T1.ITEM_ID  AND  T1.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T1.LANG+'-'+T1.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'DUEFROMTO.'+I.DUE_FROM_TO = T2.ITEM_ID  AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE";
        private string SQL_UPDATE_INVOICE = " UPDATE @_@INVOICE SET WH_ID = @WH_ID, WH_NAME = @WH_NAME, BILLING_DATE = @BILLING_DATE, BILLING_OBJECT = @BILLING_OBJECT, BILLING_OBJECT_ID = @BILLING_OBJECT_ID, DUE_FROM_TO = @DUE_FROM_TO, STATUS = @STATUS, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),IS_INVOICE_SUBMIT = @IS_INVOICE_SUBMIT,IS_AMOUNT_SUBMIT = @IS_AMOUNT_SUBMIT WHERE INVOICE_ID = @INVOICE_ID  ";
        private string STATUS = "@STATUS";
        private string TS = "@TS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";
        private string WH_NAME = "@WH_NAME";

        public bool CheckInvoiceIDUnique(string invoiceID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INVOICE_ID", invoiceID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_INVOICE_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckInvoiceIDUnique(DataBase dataBase, DbTransaction tran, string invoiceID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INVOICE_ID", invoiceID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_INVOICE_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteInvoice(List<string> invoiceID, List<string> whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < invoiceID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INVOICE_ID", invoiceID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_INVOICE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteInvoice(DataBase dataBase, DbTransaction tran, List<string> invoiceID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < invoiceID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INVOICE_ID", invoiceID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_INVOICE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllInvoice(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INVOICE_LANGUAGE.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllInvoice(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_INVOICE_LANGUAGE.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public InvoiceInfo GetInvoiceByID(string invoiceID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INVOICE.Replace("@_@", whLoginID) + " WHERE INVOICE_ID = @INVOICE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INVOICE_ID", invoiceID) };
            InvoiceInfo invoiceInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    invoiceInfo = new InvoiceInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["WH_NAME"].ToString(), reader["INVOICE_ID"].ToString(), (reader["BILLING_DATE"].ToString() != "") ? DateTime.Parse(reader["BILLING_DATE"].ToString()) : (CS00003 = new DateTime()), reader["BILLING_OBJECT"].ToString(), reader["BILLING_OBJECT_ID"].ToString(), reader["DUE_FROM_TO"].ToString(), reader["STATUS"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["IS_INVOICE_SUBMIT"].ToString(), reader["IS_AMOUNT_SUBMIT"].ToString());
                }
            }
            return invoiceInfo;
        }

        public InvoiceInfo GetInvoiceByID(DataBase dataBase, DbTransaction tran, string invoiceID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_INVOICE.Replace("@_@", whLoginID) + " WHERE INVOICE_ID = @INVOICE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@INVOICE_ID", invoiceID) };
            InvoiceInfo invoiceInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                invoiceInfo = new InvoiceInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["WH_NAME"].ToString(), reader["INVOICE_ID"].ToString(), (reader["BILLING_DATE"].ToString() != "") ? DateTime.Parse(reader["BILLING_DATE"].ToString()) : (CS00003 = new DateTime()), reader["BILLING_OBJECT"].ToString(), reader["BILLING_OBJECT_ID"].ToString(), reader["DUE_FROM_TO"].ToString(), reader["STATUS"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["IS_INVOICE_SUBMIT"].ToString(), reader["IS_AMOUNT_SUBMIT"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return invoiceInfo;
        }

        public DataSet GetInvoiceByQueryList(List<string> sqlWhere, InvoiceQueryEntity invoiceQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_INVOICE_LANGUAGE.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!invoiceQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, invoiceQuery.CurrentPage, invoiceQuery.PageSize, invoiceQuery.SortField, invoiceQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetInvoiceByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, InvoiceQueryEntity invoiceQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_INVOICE_LANGUAGE.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!invoiceQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, invoiceQuery.CurrentPage, invoiceQuery.PageSize, invoiceQuery.SortField, invoiceQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public int InsertInvoice(InvoiceInfo invoiceInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    invoiceInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Invoice_Parameters(invoiceInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_INVOICE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertInvoice(DataBase dataBase, DbTransaction tran, InvoiceInfo invoiceInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    invoiceInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Invoice_Parameters(invoiceInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_INVOICE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Invoice_Parameters(InvoiceInfo invoiceInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.WH_NAME, SqlDbType.VarChar, 30), new SqlParameter(this.INVOICE_ID, SqlDbType.VarChar, 30), new SqlParameter(this.BILLING_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.BILLING_OBJECT, SqlDbType.VarChar, 30), new SqlParameter(this.BILLING_OBJECT_ID, SqlDbType.VarChar, 30), new SqlParameter(this.DUE_FROM_TO, SqlDbType.VarChar, 10), new SqlParameter(this.STATUS, SqlDbType.VarChar, 30), new SqlParameter(this.REMARK, SqlDbType.VarChar, 300), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.IS_INVOICE_SUBMIT, SqlDbType.VarChar, 1), new SqlParameter(this.IS_AMOUNT_SUBMIT, SqlDbType.VarChar, 1) };
            paramArray[0].Value = invoiceInfo.WhID;
            paramArray[1].Value = invoiceInfo.WhName;
            paramArray[2].Value = invoiceInfo.InvoiceID;
            if (invoiceInfo.BillingDate != new DateTime())
            {
                paramArray[3].Value = invoiceInfo.BillingDate;
            }
            else
            {
                paramArray[3].Value = DBNull.Value;
            }
            paramArray[4].Value = invoiceInfo.BillingObject;
            paramArray[5].Value = invoiceInfo.BillingObjectID;
            paramArray[6].Value = invoiceInfo.DueFromTo;
            paramArray[7].Value = invoiceInfo.Status;
            if (!string.IsNullOrEmpty(invoiceInfo.Remark))
            {
                paramArray[8].Value = invoiceInfo.Remark;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            paramArray[9].Value = invoiceInfo.CreatedBy;
            paramArray[10].Value = invoiceInfo.UpdatedBy;
            if (!string.IsNullOrEmpty(invoiceInfo.IsInvoiceSubmit))
            {
                paramArray[11].Value = invoiceInfo.IsInvoiceSubmit;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(invoiceInfo.IsAmountSubmit))
            {
                paramArray[12].Value = invoiceInfo.IsAmountSubmit;
                return paramArray;
            }
            paramArray[12].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateInvoice(InvoiceInfo invoiceInfo, List<string> whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    invoiceInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Invoice_Parameters(invoiceInfo);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_INVOICE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateInvoice(DataBase dataBase, DbTransaction tran, InvoiceInfo invoiceInfo, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    invoiceInfo.WhID = whLoginID[i].Substring(0, whLoginID[i].Length - 1);
                    SqlParameter[] paras = this.Set_Invoice_Parameters(invoiceInfo);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_INVOICE.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

